﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EARS.App_Code
{
    public class EventDataClass
    {
        public void CreateEvent(Event e)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "insert into Event (EventName, EventVenue, Description, StartDate, EndDate, StartTime, EndTime, Limit, EventDeadline, OrganizerUserID, OrganizerName, OrganizerContactNo, OrganizerEmail, ExclusiveRole, ExclusiveYearofStudy, ExclusiveCourse, DateCreated) values (@EventName, @EventVenue, @Description, @StartDate, @EndDate, @StartTime, @EndTime, @Limit, @EventDeadline, @OrganizerUserID, @OrganizerName, @OrganizerContactNo, @OrganizerEmail, @ExclusiveRole, @ExclusiveYearofStudy, @ExclusiveCourse, @DateCreated)";
               
                comm.Parameters.AddWithValue("@EventName", e.EventName);
                comm.Parameters.AddWithValue("@EventVenue", e.EventVenue);
                comm.Parameters.AddWithValue("@Description", e.Description);
                comm.Parameters.AddWithValue("@StartDate", e.StartDate);
                comm.Parameters.AddWithValue("@EndDate", e.EndDate);
                comm.Parameters.AddWithValue("@StartTime", e.StartTime);
                comm.Parameters.AddWithValue("@EndTime", e.EndTime);
                comm.Parameters.AddWithValue("@Limit", e.Limit);
                comm.Parameters.AddWithValue("@EventDeadline", e.EventDeadline);
                comm.Parameters.AddWithValue("@OrganizerUserID", e.OrganizerUserID);
                comm.Parameters.AddWithValue("@OrganizerName", e.OrganizerName);
                comm.Parameters.AddWithValue("@OrganizerContactNo", e.OrganizerContactNo);
                comm.Parameters.AddWithValue("@OrganizerEmail", e.OrganizerEmail);
                comm.Parameters.AddWithValue("@ExclusiveRole", e.ExclusiveRole);
                comm.Parameters.AddWithValue("@ExclusiveYearofStudy", e.ExclusiveYearOfStudy);
                comm.Parameters.AddWithValue("@ExclusiveCourse", e.ExclusiveCourse);
                comm.Parameters.AddWithValue("@DateCreated", DateTime.Now.Date);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void UpdateEvent(Event e, string oldEventName)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Event SET EventName = @EventName, EventVenue = @EventVenue, Description = @Description, StartDate = @StartDate, EndDate = @EndDate, StartTime = @StartTime, EndTime = @EndTime, EventDeadline = @EventDeadline, OrganizerUserID = @OrganizerUserID, OrganizerName = @OrganizerName, OrganizerContactNo = @OrganizerContactNo, OrganizerEmail = @OrganizerEmail, ExclusiveYearofStudy = @ExclusiveYearofStudy, ExclusiveCourse = @ExclusiveCourse WHERE EventName = @OldEventName";
                comm.Parameters.AddWithValue("@EventName", e.EventName);
                comm.Parameters.AddWithValue("@EventVenue", e.EventVenue);
                comm.Parameters.AddWithValue("@Description", e.Description);
                comm.Parameters.AddWithValue("@StartDate", e.StartDate);
                comm.Parameters.AddWithValue("@EndDate", e.EndDate);
                comm.Parameters.AddWithValue("@StartTime", e.StartTime);
                comm.Parameters.AddWithValue("@EndTime", e.EndTime);
                comm.Parameters.AddWithValue("@EventDeadline", e.EventDeadline);
                comm.Parameters.AddWithValue("@OrganizerUserID", e.OrganizerUserID);
                comm.Parameters.AddWithValue("@OrganizerName", e.OrganizerName);
                comm.Parameters.AddWithValue("@OrganizerContactNo", e.OrganizerContactNo);
                comm.Parameters.AddWithValue("@OrganizerEmail", e.OrganizerEmail);
                comm.Parameters.AddWithValue("@ExclusiveYearofStudy", e.ExclusiveYearOfStudy);
                comm.Parameters.AddWithValue("@ExclusiveCourse", e.ExclusiveCourse);
                comm.Parameters.AddWithValue("@OldEventName", oldEventName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void DeleteEvent(string eventName)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Event WHERE EventName = @EventName";
                comm.Parameters.AddWithValue("@EventName", eventName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public Event GetEvent(string eventName)
        {
            Event evt = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Event where EventName = @EventName";
                comm.Parameters.AddWithValue("@EventName", eventName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    evt = new Event(dr["EventName"].ToString(), dr["EventVenue"].ToString(), dr["Description"].ToString(), DateTime.Parse((dr["StartDate"]).ToString()), DateTime.Parse(dr["EndDate"].ToString()), dr["StartTime"].ToString(), dr["EndTime"].ToString(), (int)dr["Limit"], DateTime.Parse(dr["EventDeadline"].ToString()), dr["OrganizerName"].ToString(), int.Parse(dr["OrganizerContactNo"].ToString()), dr["OrganizerEmail"].ToString(), dr["ExclusiveRole"].ToString(), dr["ExclusiveYearofStudy"].ToString(), dr["ExclusiveCourse"].ToString());
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return evt;
        }

        public List<string> ListOfEventName()
        {
            SqlConnection conn = null;
            List<string> eventList = new List<string>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select EventName from Event order by DateCreated desc";
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    for (int i = 0; i < 10; i++)
                    {
                        if (dr.Read())
                        {
                            eventList.Add(dr["EventName"].ToString());
                        }
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return eventList;
        }

        public List<string> ListOfEventName(string dateCreated)
        {
            SqlConnection conn = null;
            List<string> eventList = new List<string>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select EventName from Event where DateCreated < @DateCreated order by DateCreated desc";
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    for (int i = 0; i < 10; i++)
                    {
                        if (dr.Read())
                        {
                            eventList.Add(dr["EventName"].ToString());
                        }
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return eventList;
        }

        public List<string> MyOrganizedEvent(string organizerUserID)
        {
            List<string> organizedEvent = new List<string>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select EventName from Event where OrganizerUserID = @OrganizerUserID order by DateCreated desc";
                comm.Parameters.AddWithValue("@OrganizerUserID", organizerUserID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    for (int i = 0; i < 5; i++)
                    {
                        if (dr.Read())
                        {
                            organizedEvent.Add(dr["EventName"].ToString());
                        }
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return organizedEvent;
        }

        public List<string> MyOrganizedEvent(string organizerUserID, string dateCreated)
        {
            List<string> organizedEvent = new List<string>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select EventName from Event where OrganizerUserID = @OrganizerUserID and DateCreated <= @DateCreated order by DateCreated desc";
                comm.Parameters.AddWithValue("@OrganizerUserID", organizerUserID);
                comm.Parameters.AddWithValue("@DateCreated", dateCreated);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    for (int i = 0; i < 5; i++)
                    {
                        if(dr.Read())
                        {
                            organizedEvent.Add(dr["EventName"].ToString());
                        }
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return organizedEvent;
        }

        public List<string> Search(string eventName)
        {
            SqlConnection conn = null;
            List<string> eventList = new List<string>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select EventName from Event where EventName like @EventName order by DateCreated desc";
                comm.Parameters.AddWithValue("@EventName", "%" + eventName + "%");
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    for (int i = 0; i < 10; i++)
                    {
                        if (dr.Read())
                        {
                            eventList.Add(dr["EventName"].ToString());
                        }
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return eventList;
        }

        public List<string> Search(string eventName, string dateCreated)
        {
            SqlConnection conn = null;
            List<string> eventList = new List<string>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select EventName from Event where EventName like @EventName and DateCreated <= @DateCreated order by DateCreated desc";
                comm.Parameters.AddWithValue("@EventName", "%" + eventName + "%");
                comm.Parameters.AddWithValue("@DateCreated", dateCreated);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    for (int i = 0; i < 10; i++)
                    {
                        if (dr.Read())
                        {
                            eventList.Add(dr["EventName"].ToString());
                        }
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return eventList;
        }

        public string GetDateCreated(string eventName)
        {
            string dateCreated = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select DateCreated from Event where EventName = @EventName";
                comm.Parameters.AddWithValue("@EventName", eventName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    if (dr.Read())
                    {
                        dateCreated = dr["DateCreated"].ToString();
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dateCreated;
        }

        public int CheckLimit(string eventName)
        {
            int limit = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select Limit from Event where EventName = @EventName";
                comm.Parameters.AddWithValue("@EventName", eventName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    if (dr.Read())
                    {
                        limit = (int)dr["Limit"];
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return limit;
        }

        public List<string> ListOfEventToCheck (string venue)
        {
            List<string> eventList = new List<string>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select EventName from Event where EventVenue = @Venue";
                comm.Parameters.AddWithValue("@Venue", venue);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        eventList.Add(dr["EventName"].ToString());
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return eventList;
        }

        public Event GetTimeSlotInfo (string eventName)
        {
            Event evt = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select StartDate, EndDate, StartTime, EndTime from Event where EventName = @EventName";
                comm.Parameters.AddWithValue("@EventName", eventName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    if (dr.Read())
                    {
                        evt = new Event(DateTime.Parse(dr["StartDate"].ToString()), DateTime.Parse(dr["EndDate"].ToString()), dr["StartTime"].ToString(), dr["EndTime"].ToString());
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return evt;
        }
    }
}